
package com.grandtech.insurance.common;

import org.apache.poi.hssf.usermodel.*;
import org.apache.poi.hssf.util.HSSFColor;
import org.apache.poi.ss.usermodel.Cell;

import javax.servlet.http.HttpServletResponse;
import java.io.IOException;
import java.io.OutputStream;
import java.net.URLEncoder;
import java.util.ArrayList;
import java.util.List;

public class ExportExcelUtil {
  //显示的导出表的标题
  private String title;
  //导出表的列名
  private String[] rowName ;

  private List<Object[]> dataList = new ArrayList<Object[]>();

  private String color;

  private int flag;

  HttpServletResponse response;

  //构造方法，传入要导出的数据
   public ExportExcelUtil(String title, String[] rowName, List<Object[]>  dataList){
    this.dataList = dataList;
    this.rowName = rowName;
    this.title = title;
  }

  public ExportExcelUtil(String title, String[] rowName, List<Object[]>  dataList, String color, int flag){
    this.dataList = dataList;
    this.rowName = rowName;
    this.title = title;
    this.color = color;
    this.flag = flag;
  }

  /*
   * 导出数据
   * */
  public void export(HttpServletResponse response) throws Exception{

    try{
      HSSFWorkbook workbook = new HSSFWorkbook();                        // 创建工作簿对象
      HSSFSheet sheet = workbook.createSheet(title);                     // 创建工作表
      // 产生表格标题行
      //sheet样式定义【getColumnTopStyle()/getStyle()均为自定义方法 - 在下面  - 可扩展】
      HSSFCellStyle columnTopStyle = this.getColumnTopStyle(workbook);//获取列头样式对象
      HSSFCellStyle style = this.getStyle(workbook);                    //单元格样式对象
      // 定义所需列数
      int columnNum = rowName.length;
      HSSFRow rowRowName = sheet.createRow(0);                // 在索引2的位置创建行(最顶端的行开始的第二行)
      // 将列头设置到sheet的单元格中
      for(int n=0;n<columnNum;n++){
        HSSFCell cellRowName = rowRowName.createCell(n);                //创建列头对应个数的单元格
        cellRowName.setCellType(Cell.CELL_TYPE_STRING);                //设置列头单元格的数据类型
        HSSFRichTextString text = new HSSFRichTextString(rowName[n]);
        cellRowName.setCellValue(text);                                    //设置列头单元格的值

        //样品编码为文本，为了处理科学记数法问题
        if((!"".equals(color) || color!=null) && flag==1 && n==1){
          HSSFCellStyle columnTopStyle1 = this.getColumnTopStyle1(workbook,"text");
          cellRowName.setCellStyle(columnTopStyle1);
          continue;
        }

        cellRowName.setCellStyle(columnTopStyle);                        //设置列头单元格样式
      }
      //将查询出的数据设置到sheet对应的单元格中
      for(int i=0;i<dataList.size();i++){

        Object[] obj = dataList.get(i);//遍历每个对象
        HSSFRow row = sheet.createRow(i+1);//创建所需的行数

        for(int j=0; j<obj.length; j++){
          HSSFCell cell = null;   //设置单元格的数据类型
          if(j == 0){
            cell = row.createCell(j,Cell.CELL_TYPE_STRING);
            //cell.setCellType(Cell.CELL_TYPE_STRING);
            cell.setCellValue(i+1);
          }else{
            cell = row.createCell(j,Cell.CELL_TYPE_STRING);
            //if(!"".equals(obj[j]) && obj[j] != null){
            //cell.setCellType(Cell.CELL_TYPE_STRING);
            cell.setCellValue(obj[j].toString()+"".toString());                        //设置单元格的值
            // }
          }
          cell.setCellStyle(style);                                    //设置单元格样式
        }
      }
      //让列宽随着导出的列长自动适应
      for (int colNum = 1; colNum < columnNum; colNum++) {
        int columnWidth = sheet.getColumnWidth(colNum) / 256;
        for (int rowNum = 0; rowNum < sheet.getLastRowNum(); rowNum++) {
          HSSFRow currentRow;
          //当前行未被使用过
          if (sheet.getRow(rowNum) == null) {
            currentRow = sheet.createRow(rowNum);
          } else {
            currentRow = sheet.getRow(rowNum);
          }
          if (currentRow.getCell(colNum) != null) {
            HSSFCell currentCell = currentRow.getCell(colNum);
            if (currentCell.getCellType() == Cell.CELL_TYPE_STRING) {
              int length = currentCell.getStringCellValue().getBytes().length;
              if (columnWidth < length) {
                columnWidth = length;
              }

            }
          }
        }
        if(colNum == 0){
          sheet.setColumnWidth(colNum, (columnWidth-2) * 256);
        }else{
          sheet.setColumnWidth(colNum, (columnWidth+4) * 256);
        }
      }

      if(workbook !=null){
        try
        {
          //String fileName = title+".xls";
          String fileName = title+".xlsx";
          fileName = URLEncoder.encode(fileName, "UTF-8");//中文名称
          String headStr = "attachment; filename=\"" + fileName + "\"";
          response.setContentType("APPLICATION/OCTET-STREAM");
          response.setHeader("Content-Disposition", headStr);
          OutputStream out = response.getOutputStream();
          workbook.write(out);
        }
        catch (IOException e)
        {
          e.printStackTrace();
        }
      }

    }catch(Exception e){
      e.printStackTrace();
    }
  }

  /*
   * 列头单元格样式
   */
  public HSSFCellStyle getColumnTopStyle(HSSFWorkbook workbook) {

    // 设置字体
    HSSFFont font = workbook.createFont();
    //设置字体大小
    font.setFontHeightInPoints((short)12);
    //字体加粗
//    font.setBold(true);
    font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    //设置字体名字
    font.setFontName("Courier New");
    // 设置字体颜色
    //font.setColor(HSSFColor.RED.index);
    //设置样式;
    HSSFCellStyle style = workbook.createCellStyle();
    //设置底边框;
    style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
    //设置底边框颜色;
    style.setBottomBorderColor((short)8);//HSSFColor.BLACK.index（过时，替换成(short)8）下面都一样
    //设置左边框;
    style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
    //设置左边框颜色;
    style.setLeftBorderColor((short)8);
    //设置右边框;
    style.setBorderRight(HSSFCellStyle.BORDER_THIN);
    //设置右边框颜色;
    style.setRightBorderColor((short)8);
    //设置顶边框;
    style.setBorderTop(HSSFCellStyle.BORDER_THIN);
    //设置顶边框颜色;
    style.setTopBorderColor((short)8);
    //在样式用应用设置的字体;
    style.setFont(font);
    //设置自动换行;
    style.setWrapText(false);
    //设置水平对齐的样式为居中对齐;
    style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    //设置垂直对齐的样式为居中对齐;
    style.setVerticalAlignment(HSSFCellStyle.ALIGN_CENTER);

    return style;

  }

  //设置单元格格式
  public HSSFCellStyle getColumnTopStyle1(HSSFWorkbook workbook,String type) {

    // 设置字体
    HSSFFont font = workbook.createFont();
    //设置字体大小
    font.setFontHeightInPoints((short)12);
    //字体加粗
//    font.setBold(true);
    font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    //设置字体名字
    font.setFontName("Courier New");
    // 设置字体颜色
    //font.setColor(HSSFColor.RED.index);
    //设置样式;
    HSSFCellStyle style = workbook.createCellStyle();
    //设置底边框;
    style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
    //设置底边框颜色;
    style.setBottomBorderColor((short)8);//HSSFColor.BLACK.index（过时，替换成(short)8）下面都一样
    //设置左边框;
    style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
    //设置左边框颜色;
    style.setLeftBorderColor((short)8);
    //设置右边框;
    style.setBorderRight(HSSFCellStyle.BORDER_THIN);
    //设置右边框颜色;
    style.setRightBorderColor((short)8);
    //设置顶边框;
    style.setBorderTop(HSSFCellStyle.BORDER_THIN);
    //设置顶边框颜色;
    style.setTopBorderColor((short)8);
    //在样式用应用设置的字体;
    style.setFont(font);
    //设置自动换行;
    style.setWrapText(false);
    //设置水平对齐的样式为居中对齐;
    style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    //设置垂直对齐的样式为居中对齐;
    style.setVerticalAlignment(HSSFCellStyle.ALIGN_CENTER);

    HSSFDataFormat format = workbook.createDataFormat();
    if("text".equals(type)){
      style.setDataFormat(format.getFormat("@"));
    }

    return style;

  }

  /*
   * 列数据信息单元格样式
   */
  public HSSFCellStyle getStyle(HSSFWorkbook workbook) {
    // 设置字体
    HSSFFont font = workbook.createFont();
    //设置字体大小
    font.setFontHeightInPoints((short)11);
    //字体加粗
    //font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);
    //设置字体名字
    font.setFontName("Courier New");
    // 设置字体颜色
    if((!"".equals(color) || color!=null) && flag==1){
      font.setColor(HSSFColor.RED.index);
    }
    //设置样式;
    HSSFCellStyle style = workbook.createCellStyle();
    //设置底边框;
    style.setBorderBottom(HSSFCellStyle.BORDER_THIN);
    //设置底边框颜色;
    style.setBottomBorderColor((short)8);//HSSFColor.BLACK.index（过时，替换成(short)8）
    //设置左边框;
    style.setBorderLeft(HSSFCellStyle.BORDER_THIN);
    //设置左边框颜色;
    style.setLeftBorderColor((short)8);//HSSFColor.BLACK.index（过时，替换成(short)8）
    //设置右边框;
    style.setBorderRight(HSSFCellStyle.BORDER_THIN);
    //设置右边框颜色;
    style.setRightBorderColor((short)8);
    //设置顶边框;
    style.setBorderTop(HSSFCellStyle.BORDER_THIN);
    //设置顶边框颜色;
    style.setTopBorderColor((short)8);
    //在样式用应用设置的字体;
    style.setFont(font);
    //设置自动换行;
    style.setWrapText(false);
    //设置水平对齐的样式为居中对齐;
    style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
    //设置垂直对齐的样式为居中对齐;
    style.setVerticalAlignment(HSSFCellStyle.ALIGN_CENTER);

    return style;

  }
}
